<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class M_sections extends MY_Model {
	
	private $__table = 'sections';
	public function get_all_vp_sections($id=FALSE)
	{
		if($this->c_e_gl != '' && !empty($this->c_e_gl)){
			
			
				$sql = "SELECT sec.id as section_id,
							   sec.section_name,
							   lvl.level_desc,
							   sec.grade_level_id,
							   sec.adviser_id,
							   sec.max_load
						FROM sections sec
						LEFT JOIN levels lvl ON sec.grade_level_id = lvl.level_id ";
						
				if($id == FALSE){
					$sql .= " WHERE sec.section_status = 'active' AND room_assigned = 'no' AND lvl.level_id IN ({$this->c_e_gl}) ORDER BY lvl.level_id ASC";
					$query = $this->db->query($sql,array());
				}else{
					$sql .= "WHERE sec.section_status = 'active' AND sec.id = ? AND lvl.level_id IN ({$this->c_e_gl}) ORDER BY lvl.level_id ASC";
					$query = $this->db->query($sql,array($id));
				}
				return $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		}else{
			$sql = "SELECT sec.id as section_id,
						   sec.section_name,
						   lvl.level_desc,
						   sec.grade_level_id,
						   sec.adviser_id,
						   sec.max_load
					FROM sections sec
					LEFT JOIN levels lvl ON sec.grade_level_id = lvl.level_id ";
					
			if($id == FALSE){
				$sql .= " WHERE sec.section_status = 'active' AND room_assigned = 'no' ORDER BY lvl.level_id ASC";
				$query = $this->db->query($sql);
			}else{
				$sql .= "WHERE sec.section_status = 'active' AND sec.id = ? ORDER BY lvl.level_id ASC";
				$query = $this->db->query($sql,array($id));
			}
			return $query->num_rows() >= 1 ? $query->result() : FALSE;
		}
	}
	
	public function get_all_sections($id=FALSE)
	{
		$sql = "SELECT sec.id as section_id,
					   sec.section_name,
					   lvl.level_desc,
					   sec.grade_level_id,
					   sec.adviser_id,
					   sec.max_load
				FROM sections sec
				LEFT JOIN levels lvl ON sec.grade_level_id = lvl.level_id ";
				
		if($id == FALSE){
			$sql .= " WHERE sec.section_status = 'active' AND room_assigned = 'no' ORDER BY lvl.level_id ASC";
			$query = $this->db->query($sql);
		}else{
			$sql .= "WHERE sec.section_status = 'active' AND sec.id = ? ORDER BY lvl.level_id ASC";
			$query = $this->db->query($sql,array($id));
		}
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function get_the_section($id)
	{
		$sql = "SELECT sec.id as section_id,
					   sec.section_name,
					   lvl.level_desc,
					   lvl.level_code,
					   lvl.grade_type,
					   sec.grade_level_id,
					   sec.adviser_id,
					   sec.max_load,
					   concat_ws('',emp.first_name,' ',emp.last_name) as adviser,
					   rms.name as room_name,
					   rms.description as room_desc
				FROM sections sec
				LEFT JOIN levels lvl ON sec.grade_level_id = lvl.level_id 
				LEFT JOIN employees emp ON emp.emp_id = sec.adviser_id
				LEFT JOIN rooms rms ON rms.section_id = sec.id 
				WHERE sec.section_status = 'active' AND sec.id = ? ORDER BY lvl.level_id ASC
				";
		
		$query = $this->db->query($sql,array($id));

		return $query->num_rows() >= 1 ? $query->row() : FALSE;
	}
	
	public function get_all_sections_master()
	{
		$sql = "SELECT * FROM sections";
		$query = $this->db->query($sql);
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	

	/*
		query to count all grade level section for pagnation
		11/12/11/2012
	*/	
	public function count_grade_level_section()
	{
		if($this->c_e_gl != '' && !empty($this->c_e_gl)){
			$num_results = 0;
			$levels = explode( ',', $this->c_e_gl );
			
				$this->db->select('id');
				$this->db->from($this->__table);
				$this->db->where('section_status','active');
				$this->db->where_in('grade_level_id',$levels);
				$num_results = $this->db->count_all_results();
			
			return $num_results;
		}else{
			return $this->db->where('section_status','active')->count_all($this->__table);
		}
	}
	
	/*
		query to fetch all grade level section for pagnation
		11/12/11/2012
	*/	
	public function fetch_grade_level_section($limit, $start,$all = FALSE,$id = '')
	{
		if($this->c_e_gl != '' && !empty($this->c_e_gl)){
			
			if($all == FALSE)
			{
				$levels = explode( ',', $this->c_e_gl );
				
				$this->db->limit($limit, $start);
				$this->db->where_in('lvl.level_id',$levels);
				
				$query = $this->db->select('sec.id,sec.section_name,sec.section_status,lvl.level_desc,concat(emp.first_name," ",emp.last_name) as advisers_name,(max_load - current_load) as remaining_slot,max_load',FALSE)
									  ->from('sections sec')
									  ->join('levels lvl','sec.grade_level_id=lvl.level_id','LEFT')
									  ->join('employees emp','sec.adviser_id=emp.emp_id','LEFT')
									  ->where('sec.section_status','active')
									  ->where('sec.year_id',$this->c_sy->id)
									  ->order_by('sec.grade_level_id','ASC')
									  ->get();
				return $query->num_rows() >= 1 ? $query->result() : FALSE;
			}else{
				$this->db->where('lvl.level_id',$id);
				
				$query = $this->db->select('sec.id,sec.section_name,sec.section_status,lvl.level_desc,concat(emp.first_name," ",emp.last_name) as advisers_name,(max_load - current_load) as remaining_slot,max_load',FALSE)
								  ->from('sections sec')
								  ->join('levels lvl','sec.grade_level_id=lvl.level_id','LEFT')
								  ->join('employees emp','sec.adviser_id=emp.emp_id','LEFT')
								  ->where('sec.section_status','active')
								  ->where('sec.year_id',$this->c_sy->id)
								  ->order_by('sec.grade_level_id','ASC')
								  ->get();
			return $query->num_rows() >= 1 ? $query->result() : FALSE;
			}
			
		}else{
			if($all == FALSE)
			{
				$this->db->limit($limit, $start);
			}else{
				$this->db->where('lvl.level_id',$id);
			}
			
			$query = $this->db->select('sec.id,sec.section_name,sec.section_status,lvl.level_desc,concat(emp.first_name," ",emp.last_name) as advisers_name,(max_load - current_load) as remaining_slot,max_load',FALSE)
								  ->from('sections sec')
								  ->join('levels lvl','sec.grade_level_id=lvl.level_id','LEFT')
								  ->join('employees emp','sec.adviser_id=emp.emp_id','LEFT')
								  ->where('sec.section_status','active')
								  ->where('sec.year_id',$this->c_sy->id)
								  ->order_by('sec.grade_level_id','ASC')
								  ->get();
			return $query->num_rows() >= 1 ? $query->result() : FALSE;
		}
	}

	/*
		query to update section
		11/12/11/2012
	*/
	public function update_grade_level_section($input,$id)
	{
		if($this->verify_data($input,2) == FALSE)
		{
			$this->db->set($input)->where('id',$id)->update($this->__table);
			return $this->db->affected_rows() > 0 ? array('status'=>'true') : array('status'=>'false');			
		}else{
			return array('status'=>'exist');
		}
	}
	
	
	/*
		add_grade_level_section
		@param array
		adds grade level section to database
		12/11/2012
	*/
	public function add_grade_level_section($input)
	{	
		$find_duplicate = $this->db->where($input)->get($this->__table);
		if($find_duplicate->num_rows() > 0)
		{ 
			return array('status'=>'false','msg'=>'Duplicate Data Exists');
		}else
		{
			$input['year_id'] = $this->c_sy->id;//current year id
			$this->db->insert($this->__table,$input);
			$last_insert_id = $this->db->insert_id();
			if($this->db->affected_rows() > 0)
			{
				//insert subjects from curriculum to this section
				$sql = "INSERT INTO schedule (subject_id,section_id)
						SELECT c.subject_id,?
						FROM curriculum c
						WHERE c.level_id = ?
						AND c.ay_id = ? ";
				$this->db->query($sql,array($last_insert_id,$input['grade_level_id'],$this->c_sy->id));
				
				if($this->db->affected_rows() >= 1)
				{
					return array('status'=>'true','msg'=>'Section has been added.');
				}else{
					return array('status'=>'false','msg'=>'Unable to insert subjects from curriculum.');
				}
			}else{
				return array('status'=>'false','msg'=>'An error occured while inserting section.');
			}
		}
	}
	
	/*
		@param array
		add subject to schedule table 
		09/30/2013
	*/
	public function add_subject_to_section($input)
	{	
			$input['year_id'] = $this->c_sy->id;//current year id

				//insert subjects from curriculum to this section
				$sql = "INSERT INTO schedule (subject_id,section_id)
						SELECT c.subject_id,?
						FROM curriculum c
						WHERE c.level_id = ?
						AND c.ay_id = ? ";
				$this->db->query($sql,array($input['0'],$input['1'],$this->c_sy->id));
				
				if($this->db->affected_rows() >= 1)
				{
					return array('status'=>'true','msg'=>'Subject(s) has been added to the Section.');
				}else{
					return array('status'=>'false','msg'=>'Unable to insert Subject(s) from Curriculum.');
				}
			
	}
	
	/*
		query to update section_status to inactive
		11/12/11/2012
	*/
	public function destroy($id)
	{
	
		$sql = 'SELECT from ROOMS where section_id = ?';
		$query = $this->db->where('section_id',$id)->get('rooms');
		
		if($query->num_rows() >=1)
		{
			$this->db->where('section_id',$id)->delete('rooms');
			if($this->db->affected_rows() >=1)
			{
				$this->db->where('id',$id)->delete('sections');
				return $this->db->affected_rows() >=1 ? TRUE : FALSE;
			}
		}else{
			$this->db->where('id',$id)->delete('sections');
			return $this->db->affected_rows() >=1 ? TRUE : FALSE;
		
			$this->db->set('e_section_id',0)
					 ->where('e_section_id',$id)
					 ->where('e_ay_id',$this->c_sy->id)
					 ->where('e_archived',0)
					 ->where('verified',1)
					 ->update('enrollments');
					 
		}
	}
	
	/*
		query to update section_status to active
		11/12/11/2012
	*/	
	public function undestroy($id)
	{
		$data['section_status'] = 'active';
		$this->db->set($data)->where('id',$id)->update($this->__table);
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;		
	}	
	
	/* verify_data
	 * @param array
	 * @param int
	*  verifies if data entered by user is existing already in the database
	*  $strict level 1,2
	*  level 1 = if one field has existing data in database returns true else false
	*  level 2 = if all field has existing data in database returns true else false
	*  12/11/2012
	*/
	public function verify_data($data,$strict_level)
	{
			if($strict_level == 1)
			{
				$query = $this->db->or_where($data)->get($this->__table);
			}elseif($strict_level == 2){
				$query = $this->db->where($data)->get($this->__table);
			}
			return $query->num_rows() > 0 ? TRUE : FALSE;
	}
	
	
	public function get_section($section_id){
		$q = $this->db->select('id,section_name,grade_level_id')->where('id', $section_id)->get('sections');
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	public function get_section_by_grade_level($level_id)
	{
		$query = "SELECT sections.id,section_name,max_load,current_load,block_name
				  FROM sections
				  LEFT JOIN block_section bs ON bs.id = sections.block_id
				  WHERE grade_level_id = ? 
				  AND max_load != current_load 
				  AND max_load != 0
				  AND year_id = ?";
		$q = $this->db->query($query,array($level_id,$this->c_sy->id));
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function get_section_for_block($level_id)
	{
		$query = "SELECT id,section_name FROM sections 
				  WHERE grade_level_id = ? 
				  AND max_load != current_load 
				  AND max_load != 0
				  AND year_id = ?
				  AND block_id = 0";
		$q = $this->db->query($query,array($level_id,$this->c_sy->id));
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function get_section_by_grade_level_array($level_id)
	{
		$query = "SELECT id,section_name 
				  FROM sections 
				  WHERE grade_level_id = ? 
				  AND max_load != current_load 
				  AND max_load != 0
				  AND year_id = ?";
			$q = $this->db->query($query,array($level_id,$this->c_sy->id));
			
		if($q->num_rows() >= 1)
		{
			$sections = $q->result();
			$opt[''] ='--- Select Section ---';
			$opt[0] = 'No Section';
			foreach($sections as $section){
				$opt[$section->id] = $section->section_name;
			}
		}else{
			$opt[''] = 'Section is not available';
		}
		return  $opt;
	}
	
	public function set_sections_room_assigned($section_id)
	{
		$sql = 'update sections set room_assigned = ? where id = ?'; 
		$this->db->query($sql,array('yes',$section_id));
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	public function unset_sections_room_assigned($section_id)
	{
		$sql = 'update sections set room_assigned = ? where id = ?'; 
		$this->db->query($sql,array('no',$section_id));
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	public function set_sections_level_assigned($section_id)
	{
		foreach($section_id as $key => $data_to_update)
		{
			$data[$key]['id'] = $data_to_update;
			$data[$key]['level_assigned'] ='yes';
		}
		$this->db->update_batch($this->__table, $data,'id'); 
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	public function unset_sections_level_assigned($section_id)
	{
		foreach($section_id as $key => $data_to_update)
		{
			$data[$key]['id'] = $data_to_update;
			$data[$key]['level_assigned'] ='no';
		}
		$this->db->update_batch($this->__table, $data,'id'); 

		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	
	/* when assigning sections to room this will update the section room_assigned status to yes */
	public function update_section_room_assigned_status($id)
	{
		$sql = "UPDATE sections set room_assigned = 'yes' WHERE id = ?";
		$this->db->query($sql,array($id));
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	public function update_section_load($id,$mode = FALSE)
	{
		if(strtolower($mode) == 'add'){
			$sql = "UPDATE sections set current_load = (current_load +1) where id = ? AND max_load != 0";
			
		}elseif(strtolower($mode) == 'sub')
		{
			$sql = "UPDATE sections set current_load = (current_load -1) where id = ? AND max_load != 0 AND current_load != 0";
		}else{
			return FALSE;
		}
		
		$this->db->query($sql,array($id));
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function by_grade_level($level_id)
	{
		$query = "SELECT id,current_load,section_name FROM sections 
				  WHERE grade_level_id = ?";
		$q = $this->db->query($query,array($level_id));
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function get_specific_coloumn($data)
	{	
		$query = $this->db->select($data)->get('sections');
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_student_section($enrollment_id)
	{
		$sql = "SELECT level_code, 
						section_name
				FROM enrollments
				JOIN sections ON enrollments.e_section_id = sections.id
				JOIN levels ON sections.grade_level_id = levels.level_id
				WHERE enrollments.e_id = ?
				AND sections.year_id = ?";
		$query = $this->db->query($sql, array($enrollment_id,$this->c_sy->id));
		return $query->num_rows() > 0 ? $query->row() : FALSE;
	}
	
	/*
		get section data through subject
	*/
	public function get_section_id_by_subject($subject_id)
	{
		$sql = "SELECT sections.id FROM sections
				JOIN schedule ON sections.id = schedule.section_id
				JOIN subjects ON schedule.subject_id = subjects.subj_id
				WHERE subjects.subj_id = ?";
		$query = $this->db->query($sql, array($subject_id));
		return $query->num_rows() > 0 ? $query->row() : FALSE;
	}
	
	public function get_all_students_in_section($section_id,$gender = FALSE)
	{
		$sql = "SELECT 
						sections.section_name,
						concat_ws('',profiles.lastname,', ',profiles.firstname,' ',profiles.middlename) as fullname,
						profiles.gender,
						enrollments.e_student_id,
						DATE_FORMAT(enrollments.e_created_at,'%M %c,%Y') as regdate,
						enrollments.e_id as enrollment_id
				FROM enrollments
				LEFT JOIN sections ON enrollments.e_section_id = sections.id
				LEFT JOIN profiles ON profiles.profile_id = enrollments.e_profile_id
				WHERE enrollments.e_section_id = ?
				AND enrollments.e_ay_id = ?
				AND sections.year_id = ? ";

		if($gender AND in_array(strtolower($gender),['male','female']))
		{
			$sql .= " AND profiles.gender = '{$gender}'  ";
		}

		$param[] = $section_id;
		$param[] = $this->c_sy->id;
		$param[] = $this->c_sy->id;

		$sql .= " ORDER BY profiles.lastname, profiles.firstname, profiles.middlename";
		$query = $this->db->query($sql, $param);
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_all_students_in_year($year_id)
	{
		$sql = "SELECT 
						concat_ws('',profiles.lastname,' , ',profiles.firstname,' ',profiles.middlename) as fullname,
						profiles.gender,
						enrollments.e_student_id,
						DATE_FORMAT(enrollments.e_created_at,'%M %c,%Y') as regdate,
						enrollments.e_id as enrollment_id
				FROM enrollments
				LEFT JOIN profiles ON profiles.profile_id = enrollments.e_profile_id
				WHERE enrollments.e_grade_level = ?
				AND enrollments.e_ay_id = ?
				ORDER BY profiles.lastname";
		$query = $this->db->query($sql, array($year_id,$this->c_sy->id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_section_masterlist()
	{
		$sql ="SELECT sec.id, 
					  sec.section_name, 
					  level_desc,
					  block_name
			   FROM sections sec
			   LEFT JOIN levels lvl ON lvl.level_id = sec.grade_level_id
			   LEFT JOIN block_section ON block_section.id = sec.block_id
			   WHERE sec.year_id = ?";
	
		$result = $this->db->query($sql,array($this->c_sy->id));
		
		return $result->num_rows() >=1 ? $result->result() : FALSE;
	}
	
	public function get_section_masterlist_complete()
	{
		$ci =& get_instance();
		$ci->load->model(array('M_levels'));
		
		$levels = $ci->M_levels->get_all_levels();
		
		if($levels)
		{
			foreach($levels as $lvlk => $lvlv)
			{
				$sections = $this->get_section_by_grade_level($lvlv->level_id);
				$processed[$lvlk]['level']= $lvlv->level_desc;
				if($sections !== FALSE)
				{
					foreach($sections as $seck => $secv)
					{
						$processed[$lvlk]['data'][$seck]['id'] = __link($secv->id);
						$processed[$lvlk]['data'][$seck]['section_name'] = $secv->section_name;
						$processed[$lvlk]['data'][$seck]['students'] = $this->get_all_students_in_section($secv->id);
					}
				}else{
				
				}
			}
			return $processed;
		
		}else{
			return FALSE;
		}
	}
	
	public function show_all_block()
	{
		$q = $this->db->select('block_section.id as block_id,block_name,block_desc,level_desc,level_id')
					  ->join('levels','level_id = block_level','left')
					  ->get('block_section');		
					  
		if($q->num_rows() >= 1)
		{
			foreach($q->result() as $k => $v)
			{
				$data[$v->level_desc][$k]['level_id'] = $v->level_id;
				$data[$v->level_desc][$k]['bid'] = $v->block_id;
				$data[$v->level_desc][$k]['level'] = $v->level_desc;
				$data[$v->level_desc][$k]['bname'] = $v->block_name;
				$data[$v->level_desc][$k]['bdesc'] = $v->block_desc;
				$data[$v->level_desc][$k]['sections'] = $this->_get_sections_per_block($v->block_id);
			}
			return $data;
		}else{
			return FALSE;
		}
	}
	
	
	public function get_block_data($id)
	{
		$q = $this->db->where('id',$id)
					  ->join('levels','levels.level_id = block_section.block_level','LEFT')
					  ->limit(1)
					  ->get('block_section');
					  
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	private function _get_sections_per_block($id)
	{
		$q = $this->db->select('section_name,id as section_id')
					  ->where('section_status','active')
					  ->where('year_id',$this->c_sy->id)
					  ->where('block_id',$id)
					  ->get('sections');
				 
		return $q->num_rows() >=1 ? $q->result() : FALSE;
	}
	
	public function show_section_with_block()
	{
		$q = $this->db->join('block_section bs','bs.id = s.block_id','left')
				 ->join('levels l','l.level_id = s.grade_level_id','left')
				 ->where('s.section_status','active')
				 ->where('s.year_id',$this->c_sy->id)
				 ->get('sections s');
		
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function show_section_desc($id)
	{
		$q = $this->db->join('levels l','l.level_id = bs.block_level','left')
					  ->where('bs.id',$id)
					  ->get('block_section bs');
			
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	public function add_section_to_block($data,$id)
	{
		if($data)
		{
			$ids = implode(',',$data);
			$sql = "UPDATE sections 
					SET block_id = ?
					WHERE id IN ($ids)";
			
			$q = $this->db->query($sql,array($id));
			
			$good = (object)array('status'=>'true','log'=>'Successfuly added selected section to block.');
			$baad = (object)array('status'=>'false','log'=>'Unable to add section to block.');
			
			return $this->db->affected_rows() >= 1 ? $good : $baad;
			
		}else{
			return (object)array('status'=>'false','log'=>'Unable to add section to block.');
		}
	
	
	}
	
	public function insert_block_section($data)
	{
		$check_dup = $this->db->where($data)->get('block_section');
		if($check_dup->num_rows() >= 1)
		{
			return (object)array('status'=>'false','log'=>'Duplicate data for Block Settings.');
		}else{
			$this->db->insert('block_section',$data);
			$good = (object)array('status'=>'true','log'=>'Block section was successfuly created.');
			$baad = (object)array('status'=>'false','log'=>'Unable to create Block Section.');

			return $this->db->affected_rows() >=1 ? $good : $baad;
		}
	}
	
	public function get_section_by_grade_level_and_block_array($id,$level)
	{
		//check if student has been assigned to a block
		//or if a block exist
		$check_if_level_block = $this->db->select('block,e_grade_level')
										 ->where('e_id',$id)
										 ->where('verified',1)
										 ->where('e_ay_id',$this->c_sy->id)
										 ->where('e_archived',0)
										 ->get('enrollments');
		
		if($check_if_level_block->num_rows() >=1)
		{
			$d  =$check_if_level_block->row();
			if($d->block == 0)
			{
				return $this->get_section_by_grade_level_array($d->e_grade_level);
			}else{
				
				$data = $this->_get_sections_per_block($d->block);
			
				if($data)
				{
					$dropdown[''] = ' -- Select Section -- ';
					$dropdown[0] = ' Temp ';
					foreach($data as $v)
					{
						$dropdown[$v->section_id] = $v->section_name;
					}
				
					return $dropdown;
				}else{
					return array(''=>'Student is assigned to a block but no section is set.');
				}
			
			}
		}else{
			//uncomment code bellow to let student section set student even though it is not verified
			// return $this->get_section_by_grade_level_array($level);
			return array(''=>'Cannot get sections. Student is unverified.');
		}
	}
	
	
	//removes section from block
	public function remove_section_from_block($id)
	{
		$this->db->set('block_id',0)
				 ->where('id',$id)
				 ->update('sections');
		
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function remove_block($id)
	{
		// pd($id);
		$this->db->set('block',0)->where('block',$id)->where('e_ay_id',$this->c_sy->id)->update('enrollments');
		$this->db->set('block_id',0)->where('block_id',$id)->update('sections');
		$this->db->where('id',$id)->delete('block_section');
		
		return TRUE;
	}
	
	public function check_section_and_get_curriculum($id)
	{
	
		$data = $this->get_all_sections($id);

		if($data)
		{
			$data = $data[0];
			$sql = "INSERT INTO schedule (subject_id,section_id)
					SELECT c.subject_id,?
					FROM curriculum c
					WHERE c.level_id = ?
					AND c.ay_id = ? ";
								
			$this->db->query($sql,array($id,$data->grade_level_id,$this->c_sy->id));
			
			if($this->db->affected_rows() >= 1)
			{
				return (object)array('status'=>'true','msg'=>'Section has been added.');
			}else{
				return (object)array('status'=>'false','msg'=>'Unable to insert subjects from curriculum.');
			}
	
		}else{
			return (object)array('status'=>'false','msg'=>'Unable to verify section.');
		}	
	}
	
	public function update_block_section($data,$id)
	{
		$data = (object)$data;
		$set['block_name'] = htmlentities($data->named);
		$set['block_desc'] = htmlentities($data->descd);
		// pd($set);
		$this->db->set($set)->where('id',$id)->limit(1)->update('block_section');
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function get_block_by_grade_level($grade_level,$current_block = FALSE)
	{
		$sql = 'SELECT * 
				FROM block_section
				WHERE block_level = ?';
		
		if(!empty($current_block) OR $current_block > 0)
		{
			$sql .= ' AND id != '.$current_block.' ';
		}
		
		$q = $this->db->query($sql,array($grade_level));
		
		if($q->num_rows() >= 1 )
		{
			$result[''] = '--No Block Assigned --';
			foreach($q->result() as $k => $v)
			{
				$result[$v->id]= $v->block_name;
			}
			
			return $result;
		
		}else{
			return [''=>' -- No Block -- '];
		}
	}
	
	public function generate_sections_for_current_year()
	{
		$get_sections = $this->db->where('year_id',$this->c_sy->id)->get('sections');
		
		// pd($get_sections->result());
		if($get_sections->num_rows() >=1)
		{
			return TRUE;
		}else{
			$check_sections = $this->db->group_by('section_name')->get('sections');			
			if($check_sections->num_rows() >= 1)
			{
				foreach($check_sections->result() as $k => $v)
				{
					$new_sections[$k]['section_name'] = $v->section_name;
					$new_sections[$k]['section_status'] = $v->section_status;
					$new_sections[$k]['room_assigned'] = 'no';
					$new_sections[$k]['level_assigned'] = $v->level_assigned;
					$new_sections[$k]['created_at'] = NOW;
					$new_sections[$k]['grade_level_id'] = $v->grade_level_id;
					$new_sections[$k]['adviser_id'] = $v->adviser_id;
					$new_sections[$k]['max_load'] = $v->max_load;
					$new_sections[$k]['year_id'] = $this->c_sy->id;
					$new_sections[$k]['block_id'] = $v->block_id;
					$new_sections[$k]['current_load'] = 0;
				}
				
				
				$this->db->insert_batch('sections',$new_sections);
			}
			
			log_message('e','Unable to re create sections');
			return $this->db->affected_rows() >=1 ? TRUE : FALSE;
		}
	}
	
	public function get_section_summary()
	{
		$master = 'SELECT level_id as id,
						  level_desc as name
				   FROM levels
				   WHERE level_stat = "active"';

		$qmaster =$this->db->query($master);

		if($qmaster->num_rows() >= 1)
		{
			foreach($qmaster->result() as $qmask => $qmasv )
			{
				$result = $this->_get_block_by_level($qmasv->id);
				$levels[$qmask]['level'] = $qmasv->name;
				$levels[$qmask]['block_stat'] = $result->status;
				$levels[$qmask]['block_data'] = $result->data;
			}

			return ($levels);
		}else{
			return FALSE;
		}

	}
	public function _get_block_by_level($level_id)
	{
		$block = 'SELECT id,
						 block_name
				FROM block_section
				WHERE block_level = ?';
		$qblock =$this->db->query($block,[$level_id]);
		// return $qblock->num_rows() >=1 ? $qblock->result() : FALSE;
		if($qblock->num_rows() >=1)
		{
			foreach($qblock->result() as $qbk => $qbv)
			{
				$block_data[$qbk]['block'] = $qbv->block_name;
				$block_data[$qbk]['sections'] = $this->_get_section_by_block($qbv->id);
			}

			$block_status = TRUE;

		}else{
			$sect = 'SELECT id,section_name,max_load,current_load
				FROM sections
				WHERE grade_level_id = ?';
			$qsect =$this->db->query($sect,[$level_id]);
			if($qsect->num_rows() >=1){
			foreach($qsect->result() as $qsectk => $qsectv)
			{
				$male = $this->_get_student_by_section($qsectv->id,'male');
				$female = $this->_get_student_by_section($qsectv->id,'female');
				$total = $male + $female;

				$block_data[$qsectk]['section'] = $qsectv->section_name;
				$block_data[$qsectk]['id'] = __link($qsectv->id);
				$block_data[$qsectk]['male'] = $male;
				$block_data[$qsectk]['female'] = $female;
				$block_data[$qsectk]['total'] = $total;
				$block_data[$qsectk]['max'] = $qsectv->max_load;
				$block_data[$qsectk]['cur'] = $qsectv->current_load;
			}
			}else{
				$block_data = FALSE;
			}
			$block_status = FALSE;
		}


		return (object)['status'=>$block_status,'data'=>$block_data];
	}

	private function _get_section_by_block($block_id)
	{
		$sect = 'SELECT id,section_name
				FROM sections
				WHERE block_id = ?';
		$qsect =$this->db->query($sect,[$block_id]);
		if($qsect->num_rows() >=1)
		{
			foreach($qsect->result() as $qbk => $qsv)
			{
				$male = $this->_get_student_by_section($qsv->id,'male');
				$female = $this->_get_student_by_section($qsv->id,'female');
				$total = $male + $female;

				$section[$qbk]['male'] = $male;
				$section[$qbk]['sectionname'] = $qsv->section_name;
				$section[$qbk]['id'] = __link($qsv->id);
				$section[$qbk]['female'] = $female;
				$section[$qbk]['total'] = $total;
			}

			return $section;
		}else{
			return FALSE;
		}
	}

	public function _get_student_by_section($section_id,$gender)
	{
		$count = 'SELECT count(e.e_id) as total
				  FROM enrollments e
				  LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				  WHERE e.e_section_id = ?
				  AND p.gender = ?';

		$qcount = $this->db->query($count,[$section_id,$gender]);
		return $qcount->num_rows() >=1 ? $qcount->row()->total : 0;
	}

	public function get_sections_and_students_total($level)
	{
		$section = $this->by_grade_level($level);
		
		if($section){
			foreach($section as $k => $v)
			{
				$m = $this->_get_student_by_section($v->id,'male');
				$f = $this->_get_student_by_section($v->id,'female');

				$res[$k]['section_name'] = $v->section_name;
				$res[$k]['id'] = $v->id;
				$res[$k]['male'] = $m;
				$res[$k]['female'] = $f;
				$res[$k]['total'] = $f + $m;
			}

			return (object)$res;
		}else{
			return FALSE;
		}
	}

	public function get_all_students_from_block_level($level)
	{
		$block = 'SELECT id,
						 block_name
				  FROM block_section
				  WHERE block_level = ?';
		$q = $this->db->query($block,[$level]);

		if($q->num_rows() >= 1 )
		{
			foreach ($q->result() as $key => $value)
			{
				$sections[$value->block_name]['male'] = $this->_get_all_students_from_block_level($level,$value->id,'male');
				$sections[$value->block_name]['female']= $this->_get_all_students_from_block_level($level,$value->id,'female');
			}

			return $sections;
		}else{
			return FALSE;
		}
	}

	public function _get_all_students_from_block_level($level,$block,$gender)
	{
		$sql = 'SELECT concat_ws("",p.lastname," , ",p.firstname," ",p.middlename) as fullname,
					   p.dob,
					   block_name
				FROM enrollments e
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				LEFT JOIN block_section bs ON bs.id = e.block
				WHERE e.e_grade_level = ?
				AND e.block = ?
				AND p.gender = ?
				AND e.e_ay_id = ?
				AND e.verified = 1
				AND e.registrar_approved = 1
				ORDER BY p.lastname';

		$return =  $this->db->query($sql,[$level,$block,$gender,$this->c_sy->id]);
		return $return->num_rows() >= 1 ? $return->result() : FALSE;
	}

	public function get_all_students_from_block_level_by_number($level)
	{
		$block = 'SELECT id,
						 block_name
				  FROM block_section
				  WHERE block_level = ?';
		$q = $this->db->query($block,[$level]);

		// pd($this->db->last_query());
		if($q->num_rows() >= 1 )
		{
			foreach ($q->result() as $key => $value)
			{
				$sections[$value->block_name] = $this->_get_all_students_from_block_level_by_number($level,$value->id);
			}

			return $sections;
		}else{
			return FALSE;
		}
	}

	private function _get_all_students_from_block_level_by_number($level,$block)
	{
		$sql = 'SELECT concat_ws("",p.lastname," , ",p.firstname," ",p.middlename) as fullname,
					   p.dob,
					   p.gender,
					   DATE_FORMAT(p.dob,"%M %d, %Y") as bdate_formatted
				FROM enrollments e
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				LEFT JOIN block_section bs ON bs.id = e.block
				WHERE e.e_grade_level = ?
				AND e.block = ?
				AND e.e_ay_id = ?
				AND e.verified = 1
				AND e.registrar_approved = 1
				ORDER BY e.e_created_at,p.lastname';

		$return =  $this->db->query($sql,[$level,$block,$this->c_sy->id]);
		return $return->num_rows() >= 1 ? $return->result() : FALSE;
	}

	public function verify_section($section_id)
	{
		$sql = 'SELECT id FROM sections WHERE id = ? AND year_id = ?';
		$res = $this->db->query($sql,[$section_id,$this->c_sy->id]);

		return $res->num_rows() >= 1 ? TRUE : FALSE;
	}

	public function get_section_for_dd()
	{
		$sql = 'SELECT id,section_name FROM sections WHERE year_id = ? AND section_status = "active"';
		$res = $this->db->query($sql,[$this->c_sy->id]);


		if($res->num_rows() >=1)
		{
			$level[''] = '--Choose Section --';
			foreach($res->result() as $k => $v)
			{
				$level[$v->id] = $v->section_name;
			}

			return $level;
		}else{
			return [''=>'No Section Found'];
		}
	}
}
?>
